---
title: "04-PostgreSQL 索引与 MVCC"
created: 2026-08-31
tags:
  - 项目筑基
---

# PostgreSQL 索引与 MVCC

> PostgreSQL 系统讲解第四篇：性能两根支柱——索引家族与 MVCC/VACUUM。MVCC 部分和 MySQL 的 InnoDB 对照着看，理解差异比背结论有用。

## 索引家族：按场景选类型

MySQL 主要就是 B-tree（+FULLTEXT），PG 的索引类型多得多：

| 类型 | 适用 | 场景 |
| --- | --- | --- |
| **B-tree**（默认） | 可排序标量 | 等值/范围/排序，绝大多数查询 |
| **GIN**（倒排） | 复合值内部元素 | **jsonb / 数组 / 全文检索**——02 篇的三件套全靠它 |
| **GiST** | 广义搜索树 | 地理（PostGIS）、范围类型、kNN 排序 |
| **BRIN**（块区间） | 物理顺序相关的列 | 时序大表（按时间追加）的超小索引 |
| **Hash** | 等值 | 较少用，B-tree 覆盖大部分需求 |

```sql
CREATE INDEX idx_users_tags  ON users USING GIN (tags);
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops);  -- 更小的 GIN 变体
CREATE INDEX idx_logs_ts     ON logs USING BRIN (created_at);
CREATE UNIQUE INDEX ON users (lower(email));       -- 表达式索引：按小写邮箱去重
CREATE INDEX ON orders (user_id, created_at DESC); -- 多列 + 方向
CREATE INDEX idx_active ON users (email) WHERE deleted_at IS NULL;   -- 部分索引
```

对照 MySQL 的收获：**部分索引和表达式索引**是 MySQL 做不到（或很别扭）的日常工具；`EXPLAIN ANALYZE` 对应 MySQL 的 `EXPLAIN ANALYZE`（8.0+），看真实执行统计。

## EXPLAIN ANALYZE 怎么读

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;
```

读的顺序：树状计划**从内往外**；重点看三样——

- `Seq Scan`（顺序扫全表）出现在大表上 = 索引没走上的警报
- `rows` 估算 vs `actual rows`：差几个数量级 = 统计信息过时 → `ANALYZE 表名`
- `Sort Method: external merge`（外排溢出到磁盘）= 排序没被索引吃掉 + work_mem 不够

## MVCC：PG 与 InnoDB 的根本分歧

两边都是 MVCC（多版本并发控制，读不阻塞写），但**旧版本存放的位置不同**：

| | MySQL InnoDB | PostgreSQL |
| --- | --- | --- |
| 旧版本存哪 | undo log（回滚段） | **表本身**（每行带 xmin/xmax 系统列） |
| 读旧版本 | 从 undo 构建回来 | 直接找可见的旧版本行 |
| 后果 | 回滚段维护成本 | 表里堆积**死元组（dead tuples）**，要靠 VACUUM 回收 |

xmin=创建该行的事务号，xmax=删除/更新它的事务号——**PG 的 UPDATE = 插入新版本行 + 旧版本打上 xmax**，没有原地更新。

## VACUUM：PG 独有的日常

死元组不清理，表和索引会膨胀（表比"活数据"大好几倍），查询变慢。`VACUUM` 就是扫表回收死元组空间的维护操作：

- **autovacuum 默认开启**，自动后台做，一般不用管——但要知道它存在
- `VACUUM FULL` 才会真正归还磁盘空间（**锁表重写**，大表慎用；autovacuum 普通模式不锁读写）
- 长事务是死元组的头号制造者：一个久不提交的事务会让 VACUUM "够不着"它之后的死元组——**别挂着事务不提交**（对照 SQLite 篇 WAL 节的事务纪律，是同一个道理）
- 事务号回卷（wraparound）是 PG 特有的深度话题，autovacuum 会强制冻结——知道有这回事即可

> 💡 我的对照记忆钩子：**InnoDB 把"清理"藏在 undo log 的生命周期里，PG 把它摆上台面叫 VACUUM**——两种 MVCC 实现，一个主动回收一个后台回收，代价模型不同而已。

## 事务与隔离级别

```sql
BEGIN;
-- ISOLATION LEVEL: READ COMMITTED（默认）/ REPEATABLE READ / SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
```

- 默认 READ COMMITTED 与 MySQL 一致
- PG 的 **SERIALIZABLE 是真·可串行化**（SSI 实现，检测读写冲突自动回滚），MySQL 的可串行化靠锁——严格场景 PG 这边更现代
- 遇到 `ERROR: could not serialize` 重试即可（串行化冲突是设计使然，不是 bug）

---
⬅️ [[03-PostgreSQL 查询进阶|03-PostgreSQL 查询进阶]] 🏠 [[00-数据库|00-数据库]] ➡️ [[05-Python 实战 psycopg 与生态|05-Python 实战 psycopg 与生态]]